package com.bw.dao;

import com.bw.bean.Sugar1;
import com.bw.bean.Sugar2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("select sum(amount) `value` from order_info where create_time between '2023-01-06 00:00:00' and '2023-01-06 23:59:59'")
    Double getList1();

    @Select("call getOne")
    List<Sugar1> getList2();

    @Select("select a.*,RANK() over(PARTITION by a.name ORDER BY a.distance) rak from (select o.org_name name,\n" +
            "sum(actual_distance) distance,\n" +
            "count(o.org_name) num,\n" +
            "sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time)) `time`,\n" +
            "sum(actual_distance)/count(o.org_name) avgDistance,\n" +
            "sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time))/count(o.org_name) avgNum\n" +
            "from transport_task t \n" +
            "LEFT JOIN line_base_info l ON t.line_id=l.id\n" +
            "LEFT JOIN base_organ o ON o.id=l.org_id\n" +
            "GROUP BY o.org_name )a")
    List<Sugar2> getList3();

    @Select("select i1.name,count(d.sorter_emp_id) `value` from transport_task_detail d \n" +
            "LEFT JOIN transport_task t ON d.transport_task_id=t.id \n" +
            "LEFT JOIN line_base_info l ON t.line_id=l.id\n" +
            "LEFT JOIN base_organ o ON l.org_id=o.id\n" +
            "LEFT JOIN base_region_info i ON o.region_id=i.id \n" +
            "LEFT JOIN base_region_info i1 ON i.parent_id=i1.id\n" +
            "GROUP BY i1.name")
    List<Sugar1> getList4();

    @Select("select d.name,count(o.id) `value` from order_info o \n" +
            "LEFT JOIN order_cargo c ON o.id=c.order_id \n" +
            "LEFT JOIN base_dic d ON c.cargo_type=d.id \n" +
            "GROUP BY d.name")
    List<Sugar1> getList5();

    @Select("select i1.name,count(d.id) `value` from express_task_delivery d \n" +
            "LEFT JOIN base_organ o ON d.org_id=o.id\n" +
            "LEFT JOIN base_region_info i ON o.region_id=i.id\n" +
            "LEFT JOIN base_region_info i1 ON i.parent_id=i1.id\n" +
            "GROUP BY i1.name")
    List<Sugar1> getList6();

    @Select("select '运输次数' name,count(*) `value` from transport_task where create_time>='2023-01-06 00:00:00' and '2023-01-06 23:59:59'\n" +
            "UNION\n" +
            "select '公里数' name,sum(actual_distance) `value` from transport_task where create_time>='2023-01-06 00:00:00' and '2023-01-06 23:59:59'\n" +
            "UNION \n" +
            "select '时长' name,sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time)) `value` from transport_task where create_time>='2023-01-06 00:00:00' and '2023-01-06 23:59:59'")
    List<Sugar1> getList7();

    @Select("select o.org_name name,\n" +
            "sum(t.actual_distance) distance,\n" +
            "sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time)) `time`,\n" +
            "count(t.id) num,\n" +
            "sum(t.actual_distance)/count(t.id) avgDistance,\n" +
            "sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time))/count(t.id) avgNum\n" +
            "from transport_task t \n" +
            "LEFT JOIN line_base_info l ON t.line_id=l.id \n" +
            "LEFT JOIN base_organ o ON l.org_id=o.id \n" +
            "GROUP BY o.org_name")
    List<Sugar2> getList8();

    @Select("select count(*) `value` from transport_task where create_time between '2023-01-06 00:00:00' and '2023-01-06 23:59:59'")
    double getList9();
}
